/*
 =============================================================================
 FILE...............: FlightDAO.java
 COMMENTS...........: Class implemented by using the DAO (Data Access Object)
 					  design pattern for manipulating Flight objects while
 					  accessing the database. This class extends the GenericDAO
 					  abstract class.
 =============================================================================
 AUTHOR(S)..........: Everton Ranielly de Sousa Cavalcante
 					  Department of Informatics and Applied Mathematics
 					  Federal University of Rio Grande do Norte
 LAST MODIFICATION..: September 17th, 2012
 ============================================================================= 					  
*/


/* ========================================================================= */
/* PACKAGES			                                                         */
/* ------------------------------------------------------------------------- */
package dao;


/* ========================================================================= */
/* IMPORTS			                                                         */
/* ------------------------------------------------------------------------- */
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Airport;
import model.Flight;


/* ========================================================================= */
/* CLASS FlightDAO                                                           */
/* ------------------------------------------------------------------------- */
/**
 * Class implemented by using the DAO (Data Access Object) design pattern for 
 * manipulating <code>Flight</code> objects while accessing the database. 
 * This class extends the <code>GenericDAO</code> abstract class.
 * @author Everton Cavalcante
 */
public class FlightDAO extends GenericDAO<Flight, String>
{
	/** Default constructor */
	public FlightDAO()
	{ /* Empty */ }
	
	
	/**
	 * Inserts a flight
	 * @param flight Flight to insert
	 */
	public void insert(Flight flight)
	{		
		try 
		{
			// Opens a connection 
			openConnection();
			
			// Departure and arrival airports
			Airport fromAirport = flight.getFrom();
			Airport toAirport = flight.getTo();
			
			String from = (fromAirport.getAirportIATACode().equals("")) ? fromAirport.getAirportICAOCode() 	: fromAirport.getAirportIATACode();
			String to 	= (toAirport.getAirportIATACode().equals("")) 	? toAirport.getAirportICAOCode() 	: toAirport.getAirportIATACode();
			
			// SQL query to be executed
			String query = "INSERT INTO flights(airline, identification, departureAirport, arrivalAirport, flightdate, flighttime, aircraft) VALUES(?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(query);
			
			// Data to be inserted		
			pstmt.setString(1, flight.getAirline());
			pstmt.setString(2, flight.getIdentification());
			pstmt.setString(3, from);
			pstmt.setString(4, to);
			pstmt.setString(5, flight.getFlightDate().substring(0, flight.getFlightDate().indexOf(" ")));
			pstmt.setString(6, flight.getFlightDate().substring(flight.getFlightDate().indexOf(" ")));
			pstmt.setString(7, flight.getAircraft());
			
			// Executes query
			pstmt.execute();
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when inserting flight:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e) 
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
	}
	
	
	/**
	 * Updates flight
	 * @param flight Flight data to update
	 * @param code Flight code
	 */
	public void update(Flight flight, String code)
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			// Departure and arrival airports
			Airport fromAirport = flight.getFrom();
			Airport toAirport = flight.getTo();
					
			String from = (fromAirport.getAirportIATACode().equals("")) ? fromAirport.getAirportICAOCode() 	: fromAirport.getAirportIATACode();
			String to 	= (toAirport.getAirportIATACode().equals("")) 	? toAirport.getAirportICAOCode() 	: toAirport.getAirportIATACode();
				
			// SQL query to be executed
			String query = "UPDATE flights SET airline = ?, identification = ?, departureAirport = ?, arrivalAirport = ?, flightdate = ?, " +
				"flighttime = ?, aircraft = ? WHERE identification = ?";
			pstmt = conn.prepareStatement(query);
			
			// Data to be updated	
			pstmt.setString(1, flight.getAirline());
			pstmt.setString(2, flight.getIdentification());
			pstmt.setString(3, from);
			pstmt.setString(4, to);
			pstmt.setString(5, flight.getFlightDate().substring(0, flight.getFlightDate().indexOf(" ")));
			pstmt.setString(6, flight.getFlightDate().substring(flight.getFlightDate().indexOf(" ")));
			pstmt.setString(7, flight.getAircraft());
			pstmt.setString(8, code);
				
			// Executes query
			pstmt.execute();
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when updating flight:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e) 
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
	}
	
	
	/**
	 * Deletes flight
	 * @param code Flight code
	 */
	public void delete(String code)
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			// SQL query to be executed
			String query = "DELETE FROM flights WHERE identification = ?";
			pstmt = conn.prepareStatement(query);
				
			pstmt.setString(1, code);
			
			// Executes query
			pstmt.execute();
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when deleting flight:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e) 
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
	}
	
	
	/**
	 * Returns the specified flight
	 * @param code Flight code
	 * @return Specified flight
	 */
	public Flight get(String code)
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			// SQL query to be executed
			String query = "SELECT airlineshortname, identification, departureAirport, arrivalAirport, flightdate, flighttime, " +
				"flights.aircraft FROM flights JOIN airlines ON flights.airline = airlines.airlinecode JOIN aircrafts ON " +
				"flights.aircraft = aircrafts.aircraft WHERE identification = '" + code + "'";
			
			stmt = conn.prepareStatement(query);
				
			// Flight to be returned
			Flight result = null;
			
			// AirportDAO instance
			AirportDAO airportDAO = new AirportDAO();
				
			// Executes query
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next())
			{
				result = new Flight();
				
				result.setAirline(rs.getString("airlineshortname"));
				result.setIdentification(rs.getString("identification"));
				result.setFrom(airportDAO.get(rs.getString("departureAirport")));
				result.setTo(airportDAO.get(rs.getString("arrivalAirport")));
				result.setFlightDate(rs.getString("flightdate") + " " + rs.getString("flighttime"));
				result.setAircraft(rs.getString("aircraft"));
			}
				
			// Returns the specified flight
			return result;
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when searching flight:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e) 
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
		
		return null;
	}
	
	
	/**
	 * Returns the specified flight by its index in the database table
	 * @param index Flight index in the database table
	 * @return Specified flight
	 */
	public Flight get(int index)
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			// SQL query to be executed
			String query = "SELECT airlineshortname, identification, departureAirport, arrivalAirport, flightdate, flighttime, " +
				"flights.aircraft FROM flights JOIN airlines ON flights.airline = airlines.airlinecode JOIN aircrafts ON " +
				"flights.aircraft = aircrafts.aircraft WHERE flights.flightid = '" + index + "'";
			
			stmt = conn.prepareStatement(query);
				
			// Flight to be returned
			Flight result = null;
			
			// AirportDAO instance
			AirportDAO airportDAO = new AirportDAO();
				
			// Executes query
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next())
			{
				result = new Flight();
				
				result.setAirline(rs.getString("airlineshortname"));
				result.setIdentification(rs.getString("identification"));
				result.setFrom(airportDAO.get(rs.getString("departureAirport")));
				result.setTo(airportDAO.get(rs.getString("arrivalAirport")));
				result.setFlightDate(rs.getString("flightdate") + " " + rs.getString("flighttime"));
				result.setAircraft(rs.getString("aircraft"));
			}
				
			// Returns the specified flight
			return result;
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when searching flight:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e) 
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
		
		return null;
	}
	
	
	/**
	 * Returns the flight index in the database table by the flight identification
	 * @param identification Flight identification
	 * @return Flight index in the database table
	 */
	public int getFlightIndex(String identification) 
	{
		try
		{
			// Opens a connection
			openConnection();
			
			// SQL query to be executed
			String query = "SELECT * FROM flights WHERE identification = '" + identification + "'";
			stmt = conn.prepareStatement(query);
			
			// Index to be returned
			int index = 0;
									
			// Executes query
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next())
			{ index = rs.getInt("flightid"); }
			
			return index;
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when searching flight:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e) 
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
		
		return 0;
	}
	
	
	/**
	 * Returns all registered flights
	 * @return Registered flights
	 */
	public List<Flight> getAll()
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			// SQL query to be executed
			String query = "SELECT airlineshortname, identification, departureAirport, arrivalAirport, flightdate, flighttime, " +
				"flights.aircraft FROM flights JOIN airlines ON flights.airline = airlines.airlinecode JOIN aircrafts ON " +
				"flights.aircraft = aircrafts.aircraft ";
			
			// List of flights
			List<Flight> flights = new ArrayList<Flight>();
			
			// AirportDAO instance
			AirportDAO airportDAO = new AirportDAO();
			
			// Executes query
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next())
			{
				// Current flight
				Flight flight = new Flight();
				
				flight.setAirline(rs.getString("airlineshortname"));
				flight.setIdentification(rs.getString("identification"));
				flight.setFrom(airportDAO.get(rs.getString("departureAirport")));
				flight.setTo(airportDAO.get(rs.getString("arrivalAirport")));
				flight.setFlightDate(rs.getString("flightdate") + " " + rs.getString("flighttime"));
				flight.setAircraft(rs.getString("aircraft"));
				
				// Adds the current flight
				flights.add(flight);
			}
			
			// Returns registered flights
			return flights;
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when searching flights:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e) 
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
		
		return null;
	}
	
	
	/**
	 * Return all available flights according to the specified criteria
	 * @param from Departure airport
	 * @param to Arrival airport
	 * @param departureDate Departure date
	 * @return List of available flights
	 */
	public List<Flight> getAvailableFlights(String from, String to, String departureDate)
	{
		// Validate airport codes
		if ((from.length() != 3 && from.length() != 4) || (from.length() != 3 && from.length() != 4))
		{
			System.out.println("Error:\nInvalid airport code");
			System.exit(1);
		}
		else
		{
			try
			{
				// Opens a connection 
				openConnection();
				
				// SQL query to be executed
				String query = "SELECT airlineshortname, identification, departureAirport, arrivalAirport, flightdate, flighttime, " +
					"flights.aircraft FROM flights JOIN airlines ON flights.airline = airlines.airlinecode JOIN aircrafts ON " +
					"flights.aircraft = aircrafts.aircraft WHERE (departureAirport = '" + from + "' AND arrivalAirport = '" + to + "') " +
					"AND (flightdate = '" + departureDate + "') AND (availableseats > 0)";
				
				// List of flights
				List<Flight> availableFlights = new ArrayList<Flight>();

				// AirportDAO instance
				AirportDAO airportDAO = new AirportDAO();
				
				// Executes query
				ResultSet rs = stmt.executeQuery(query);
				while (rs.next())
				{
					// Current flight
					Flight flight = new Flight();
					
					flight.setAirline(rs.getString("airlineshortname"));
					flight.setIdentification(rs.getString("identification"));
					flight.setFrom(airportDAO.get(rs.getString("departureAirport")));
					flight.setTo(airportDAO.get(rs.getString("arrivalAirport")));
					flight.setFlightDate(rs.getString("flightdate") + " " + rs.getString("flighttime"));
					flight.setAircraft(rs.getString("aircraft"));
					
					// Adds the current flight
					availableFlights.add(flight);
				}
				
				return availableFlights;
			}
			catch (SQLException e) 
			{ 
				System.out.println("Error when searching flights:\n" + e.getMessage());
				System.exit(1);
			}
			catch (IOException e) 
			{
				e.printStackTrace();
				System.exit(1);
			}
			finally
			{ closeConnection(); }
			
			return null;
		}
		
		return null;
	}
}
